import pandas as pd
from faker import Faker
import random
import json
import os
import shutil

# 清空 data 目录，避免旧数据干扰
if os.path.exists('data'):
    shutil.rmtree('data')
os.makedirs('data', exist_ok=True)

fake = Faker('zh_CN')

def gen_id():
    return int(str(random.randint(100000, 999999)))

N = 120  # 每个表生成120条，后续只用前100条

# 1. 生成用户数据
users = []
user_ids = set()
while len(users) < N:
    uid = gen_id()
    if uid in user_ids:
        continue
    user_ids.add(uid)
    users.append({
        'UserID': uid,
        'Username': fake.user_name(),
        'Password': fake.sha256(),
        'Email': fake.email(),
        'Phone': fake.phone_number(),
        'RegistrationDate': fake.date_time_this_decade(),
        'Balance': round(random.uniform(0, 1000), 2),
        'CreditRating': random.randint(1, 5),
        'Status': random.choice(['active', 'banned', 'unverified']),
        'Role': 'user'
    })
users = users[:100]
# 设置第一个用户为管理员
users[0]['Role'] = 'admin'
users_df = pd.DataFrame(users)
users_df.to_csv('data/Users.csv', index=False, encoding='utf-8')

# 2. 生成装备数据
equipments = []
equipment_ids = set()
while len(equipments) < N:
    eid = gen_id()
    if eid in equipment_ids:
        continue
    equipment_ids.add(eid)
    seller = random.choice(users)
    equipments.append({
        'EquipmentID': eid,
        'SellerID': seller['UserID'],
        'EquipmentName': fake.word() + str(random.randint(1, 100)),
        'Category': random.choice(['武器', '防具', '饰品', '材料']),
        'Level': random.randint(1, 100),
        'Rarity': random.choice(['common', 'uncommon', 'rare', 'epic', 'legendary']),
        'Attributes': json.dumps({'攻击': random.randint(10, 100), '防御': random.randint(5, 50)}),
        'Price': round(random.uniform(10, 500), 2),
        'ListedDate': fake.date_time_this_year(),
        'Status': random.choice(['active', 'sold', 'removed']),
        'ImageURL': fake.image_url()
    })
equipments = equipments[:100]
equipments_df = pd.DataFrame(equipments)
equipments_df.to_csv('data/Equipments.csv', index=False, encoding='utf-8')

# 3. 生成交易数据
transactions = []
transaction_ids = set()
while len(transactions) < N:
    tid = gen_id()
    if tid in transaction_ids:
        continue
    transaction_ids.add(tid)
    equip = random.choice(equipments)
    seller_id = equip['SellerID']
    buyer = random.choice([u for u in users if u['UserID'] != seller_id])
    transactions.append({
        'TransactionID': tid,
        'EquipmentID': equip['EquipmentID'],
        'SellerID': seller_id,
        'BuyerID': buyer['UserID'],
        'TransactionDate': fake.date_time_this_year(),
        'Price': equip['Price'],
        'Status': random.choice(['pending_payment', 'completed', 'cancelled', 'refunded']),
        'PaymentMethod': random.choice(['支付宝', '微信', '银行卡']),
        'CompletionDate': fake.date_time_this_year()
    })
transactions = transactions[:100]
transactions_df = pd.DataFrame(transactions)
transactions_df.to_csv('data/Transactions.csv', index=False, encoding='utf-8')

# 4. 生成钱包记录数据
wallet_records = []
wallet_ids = set()
while len(wallet_records) < N:
    wid = gen_id()
    if wid in wallet_ids:
        continue
    wallet_ids.add(wid)
    user = random.choice(users)
    transaction = random.choice(transactions + [None])
    wallet_records.append({
        'RecordID': wid,
        'UserID': user['UserID'],
        'TransactionID': transaction['TransactionID'] if transaction else None,
        'Type': random.choice(['deposit', 'withdraw', 'income', 'expense']),
        'Amount': round(random.uniform(1, 500), 2),
        'Balance': round(random.uniform(0, 2000), 2),
        'Timestamp': fake.date_time_this_year()
    })
wallet_records = wallet_records[:100]
wallet_records_df = pd.DataFrame(wallet_records)
wallet_records_df.to_csv('data/WalletRecords.csv', index=False, encoding='utf-8', na_rep='\\N')

# 5. 生成购物车数据
cart = []
cart_ids = set()
while len(cart) < N:
    cid = gen_id()
    if cid in cart_ids:
        continue
    cart_ids.add(cid)
    user = random.choice(users)
    equipment = random.choice(equipments)
    cart.append({
        'CartID': cid,
        'UserID': user['UserID'],
        'EquipmentID': equipment['EquipmentID'],
        'AddedDate': fake.date_time_this_year()
    })
cart = cart[:100]
cart_df = pd.DataFrame(cart)
cart_df.to_csv('data/Cart.csv', index=False, encoding='utf-8')

# 6. 生成价格变动消息提醒数据
price_change_msgs = []
msg_ids = set()
while len(price_change_msgs) < N:
    mid = gen_id()
    if mid in msg_ids:
        continue
    msg_ids.add(mid)
    user = random.choice(users)
    equipment = random.choice(equipments)
    old_price = round(random.uniform(10, 500), 2)
    new_price = round(random.uniform(10, 500), 2)
    while new_price == old_price:
        new_price = round(random.uniform(10, 500), 2)
    price_change_msgs.append({
        'MessageID': mid,
        'UserID': user['UserID'],
        'EquipmentID': equipment['EquipmentID'],
        'OldPrice': old_price,
        'NewPrice': new_price,
        'ChangeDate': fake.date_time_this_year(),
        'IsRead': random.choice([True, False])
    })
price_change_msgs = price_change_msgs[:100]
price_change_msgs_df = pd.DataFrame(price_change_msgs)
price_change_msgs_df.to_csv('data/PriceChangeMessages.csv', index=False, encoding='utf-8')

print("所有CSV文件已生成到 data 文件夹。")

# 读取CSV文件
users_df = pd.read_csv('../data/Users.csv')
equipments_df = pd.read_csv('../data/Equipments.csv')
transactions_df = pd.read_csv('../data/Transactions.csv')
wallet_records_df = pd.read_csv('../data/WalletRecords.csv')
cart_df = pd.read_csv('../data/Cart.csv')
price_change_msgs_df = pd.read_csv('../data/PriceChangeMessages.csv')

print("\n基于CSV文件的外键约束检查结果：")

# 1. Equipments.SellerID in Users.UserID
equipments_ok = equipments_df['SellerID'].isin(users_df['UserID']).all()
print(f"Equipments.SellerID -> Users.UserID: {'合格' if equipments_ok else '不合格'}")

# 2. Transactions.EquipmentID in Equipments.EquipmentID
transactions_equip_ok = transactions_df['EquipmentID'].isin(equipments_df['EquipmentID']).all()
print(f"Transactions.EquipmentID -> Equipments.EquipmentID: {'合格' if transactions_equip_ok else '不合格'}")

# 3. Transactions.SellerID/BuyerID in Users.UserID
transactions_seller_ok = transactions_df['SellerID'].isin(users_df['UserID']).all()
transactions_buyer_ok = transactions_df['BuyerID'].isin(users_df['UserID']).all()
print(f"Transactions.SellerID -> Users.UserID: {'合格' if transactions_seller_ok else '不合格'}")
print(f"Transactions.BuyerID -> Users.UserID: {'合格' if transactions_buyer_ok else '不合格'}")

# 4. WalletRecords.UserID in Users.UserID
wallet_user_ok = wallet_records_df['UserID'].isin(users_df['UserID']).all()
print(f"WalletRecords.UserID -> Users.UserID: {'合格' if wallet_user_ok else '不合格'}")
# 4.1 WalletRecords.TransactionID in Transactions.TransactionID (允许为缺失/空)
wallet_trans_ok = wallet_records_df['TransactionID'].isna() | wallet_records_df['TransactionID'].isin(transactions_df['TransactionID'])
wallet_trans_ok = wallet_trans_ok.all()
print(f"WalletRecords.TransactionID -> Transactions.TransactionID: {'合格' if wallet_trans_ok else '不合格'}")

# 5. Cart.UserID in Users.UserID, Cart.EquipmentID in Equipments.EquipmentID
cart_user_ok = cart_df['UserID'].isin(users_df['UserID']).all()
cart_equip_ok = cart_df['EquipmentID'].isin(equipments_df['EquipmentID']).all()
print(f"Cart.UserID -> Users.UserID: {'合格' if cart_user_ok else '不合格'}")
print(f"Cart.EquipmentID -> Equipments.EquipmentID: {'合格' if cart_equip_ok else '不合格'}")

# 6. PriceChangeMessages.UserID in Users.UserID, EquipmentID in Equipments.EquipmentID
msg_user_ok = price_change_msgs_df['UserID'].isin(users_df['UserID']).all()
msg_equip_ok = price_change_msgs_df['EquipmentID'].isin(equipments_df['EquipmentID']).all()
print(f"PriceChangeMessages.UserID -> Users.UserID: {'合格' if msg_user_ok else '不合格'}")
print(f"PriceChangeMessages.EquipmentID -> Equipments.EquipmentID: {'合格' if msg_equip_ok else '不合格'}")